#region
/* Adoor.NET - Object/Relational mapping framework leveraging ADO.NET`s powerful disconnected mode.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *  
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *  
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
#endregion

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Adoor.Relational;
using Adoor.Relational.SqlDriver;

using System.ComponentModel;
 
namespace Adoor.Relational.RelationalSource
{
    using System.Data.Common;
    using System.Data.SqlClient;

    [System.ComponentModel.ToolboxItem(true), System.ComponentModel.DesignTimeVisible(true)]    
    public class SqlRelationalSource : CommonDbRelationalSource
    {


        public SqlRelationalSource(
            ISqlDriver sqlDriver
            )
            : base(SqlClientFactory.Instance, sqlDriver)
        {
        }

        public SqlRelationalSource(
            ISqlDriver sqlDriver
            , SqlConnectionStringBuilder connectionStringBuilder)
            : base(SqlClientFactory.Instance, sqlDriver, connectionStringBuilder)
        {
        }

        public SqlRelationalSource(SqlConnectionStringBuilder connectionStringBuilder)
            :
            this(new Adoor.Relational.SqlDriver.TransactSqlDriver(), connectionStringBuilder)
        {
        }

        public SqlRelationalSource()
            :
            this(new Adoor.Relational.SqlDriver.TransactSqlDriver())
        {
            
        }

       

        protected override void EscapeCommandText(ref string commandText, IDataParameterCollection parameters)
        {
            string[] paramNames = new string[parameters.Count];
            for (int i = 0; i < parameters.Count; i++)
                paramNames[i] = "@p" + (i + 1).ToString();
            commandText = string.Format(commandText, paramNames);
        }


        private static bool IsInPrimaryKey(DataColumn dc, DataTable table)
        {
            foreach (DataColumn pk in table.PrimaryKey)
                if (string.Compare(pk.ColumnName, dc.ColumnName, true) == 0)
                    return true;
            return false;
        }

        public SqlCommand GetUpdateCommand(DataTable table, string updateCommand, ICollection<DataColumn> dataColumns)
        {
            SqlCommand result = (SqlCommand)dbProviderFactory.CreateCommand();
            int i = 0;
            foreach (DataColumn dc in dataColumns)
            {
                if (dc.Expression != "" || IsInPrimaryKey(dc, table) || dc.ReadOnly)
                    continue;
                SqlParameter p = new SqlParameter();
                p.ParameterName = "@p" + (++i).ToString();
                p.SourceColumn = dc.ColumnName;
                p.DbType = Type2DbType(dc.DataType);
                //p.SqlDbType = Type2SqlDbType(dc.DataType);
                result.Parameters.Add(p);
            }
            foreach (DataColumn dc in table.PrimaryKey)
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = "@p" + (++i).ToString();
                p.SourceColumn = dc.ColumnName;
                p.SourceVersion = DataRowVersion.Original;
                p.DbType = Type2DbType(dc.DataType);
                //p.SqlDbType = Type2SqlDbType(dc.DataType);
                result.Parameters.Add(p);
            }
            if (updateWhereAll)
                foreach (DataColumn dc in dataColumns)
                {
                    SqlParameter p = new SqlParameter();
                    p.SourceColumn = dc.ColumnName;
                    p.SourceVersion = DataRowVersion.Original;
                    p.DbType = Type2DbType(dc.DataType);
                    //p.SqlDbType = Type2SqlDbType(dc.DataType);
                    result.Parameters.Add(p);
                }
            EscapeCommandText(ref updateCommand, result.Parameters);
            result.CommandText = updateCommand;
            return result;
        }

        public SqlCommand GetInsertCommand(DataTable table, bool identity, string insertCommand, ICollection<DataColumn> dataColumns)
        {
            // for IDENTITY_INSERT see http://www.tkdinesh.com/faq/ans/identinsert.html
            if (identity)
                insertCommand = string.Format("SET IDENTITY_INSERT [{0}] ON; {1}; SET IDENTITY_INSERT [{0}] OFF", table.TableName, insertCommand);

            SqlCommand result = (SqlCommand)dbProviderFactory.CreateCommand();
            int i = 0;
            foreach (DataColumn dc in dataColumns)
            {
                if (dc.Expression != "" || dc.ReadOnly)
                    continue;
                SqlParameter p = new SqlParameter();
                p.ParameterName = "@p" + (++i).ToString();
                p.SourceColumn = dc.ColumnName;
                p.DbType = Type2DbType(dc.DataType);
                //p.SqlDbType = Type2SqlDbType(dc.DataType);
                result.Parameters.Add(p);
            }
            EscapeCommandText(ref insertCommand, result.Parameters);
            result.CommandText = insertCommand;
            return result;
        }

        public SqlCommand GetDeleteCommand(DataTable table, string deleteCommand, ICollection<DataColumn> dataColumns)
        {
            SqlCommand result = (SqlCommand)dbProviderFactory.CreateCommand();
            int i = 0;
            foreach (DataColumn dc in table.PrimaryKey)
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = "@p" + (++i).ToString();
                p.SourceColumn = dc.ColumnName;
                p.SourceVersion = DataRowVersion.Original;
                p.DbType = Type2DbType(dc.DataType);
                //p.SqlDbType = Type2SqlDbType(dc.DataType);
                result.Parameters.Add(p);
            }
            if (updateWhereAll)
            {
                foreach (DataColumn dc in dataColumns)
                {
                    SqlParameter p = new SqlParameter();
                    p.ParameterName = "@p" + (++i).ToString();
                    p.SourceColumn = dc.ColumnName;
                    p.SourceVersion = DataRowVersion.Original;  // ignored ?
                    p.DbType = Type2DbType(dc.DataType);
                    //p.SqlDbType = Type2SqlDbType(dc.DataType);
                    result.Parameters.Add(p);
                }
            }
            EscapeCommandText(ref deleteCommand, result.Parameters);
            result.CommandText = deleteCommand;
            return result;
        }

        public override DbDataAdapter GetDataAdapter(DataTable table,
            string selectCommand, string updateCommand, string insertCommand, string deleteCommand,
            ICollection<DataColumn> dataColumns)
        {

            SqlDataAdapter result = (SqlDataAdapter)dbProviderFactory.CreateDataAdapter();
            result.SelectCommand = (SqlCommand)dbProviderFactory.CreateCommand();
            result.SelectCommand.Connection = (SqlConnection)dbProviderFactory.CreateConnection();
            result.SelectCommand.Connection.ConnectionString = ConnectionStringBuilder.ConnectionString;
            result.SelectCommand.CommandText = selectCommand;
            result.SelectCommand.CommandText = selectCommand;
            //if (log.IsDebugEnabled)
            result.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
            
            //SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(SqlDataAdapter);

            // Update command
            result.UpdateCommand = GetUpdateCommand(table, updateCommand, dataColumns);
            result.UpdateCommand.Connection = result.SelectCommand.Connection;

            // Insert command
            result.InsertCommand = GetInsertCommand(table, false, insertCommand, dataColumns);
            result.InsertCommand.Connection = result.SelectCommand.Connection;

            // Delete command
            result.DeleteCommand = GetDeleteCommand(table, deleteCommand, dataColumns);
            result.DeleteCommand.Connection = result.SelectCommand.Connection;

            return result;
        }

        public void OnRowUpdating(object sender, SqlRowUpdatingEventArgs args)
        {
            AdoorLoger.Debug(Tracer.GetTrace(args.Command.CommandText, args.Command));

            if(args.Errors != null)
                AdoorLoger.Error(Tracer.GetTrace(args.Command.CommandText, args.Command));  
        }

        protected override RelationalSourceTrace CreateTracer()
        {
            return new Adoor.Relational.RelationalSource.SqlRelationSourceTrace();
        }

        //private int maxRetry = 10;
        //private const int sleep = 500;

        //public override void ExecuteNonQuery(string commandText, System.Collections.ICollection args)
        //{
        //    int retryCnt = 0;
        //    do
        //    {
        //        try
        //        {
        //            base.ExecuteNonQuery(commandText, args);
        //            return;
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            if (log.IsDebugEnabled)
        //                log.Debug("SqlException Number " + sqlEx.Number + " class " + sqlEx.Class, sqlEx);

        //            if ((sqlEx.Number == 1205) && (sqlEx.Class == 13))
        //            {
        //                if (retryCnt < maxRetry)
        //                {
        //                    retryCnt++;
        //                    System.Threading.Thread.Sleep(sleep);
        //                }
        //                else
        //                    throw new Adoor.Object.Disconnected.TransactionRetryException(sqlEx.Message
        //                        + "\n"
        //                        + commandText
        //                        , sqlEx);
        //            }
        //            else
        //                throw;
        //        }
        //    } while (true);
        //}

        //public override void ExecuteNonQuery(string commandText, IDictionary<string, object> args)
        //{
        //    int retryCnt = 0;
        //    do
        //    {
        //        try
        //        {
        //            base.ExecuteNonQuery(commandText, args);
        //            return;
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            if (log.IsDebugEnabled)
        //                log.Debug("SqlException Number " + sqlEx.Number + " class " + sqlEx.Class, sqlEx);

        //            if ((sqlEx.Number == 1205) && (sqlEx.Class == 13))
        //            {
        //                if (retryCnt < maxRetry)
        //                {
        //                    retryCnt++;
        //                    System.Threading.Thread.Sleep(sleep);
        //                }
        //                else
        //                    throw new Adoor.Object.Disconnected.TransactionRetryException(sqlEx.Message 
        //                        + "\n" 
        //                        + commandText
        //                        , sqlEx);
        //            }
        //            else
        //                throw;
        //        }
        //    } while (true);
        //}

        //public override IDataReader ExecuteReader(RelationalQuery query, System.Collections.ICollection args)
        //{
        //    int retryCnt = 0;
        //    do
        //    {
        //        try
        //        {
        //            return base.ExecuteReader(query, args);
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            if (log.IsDebugEnabled)
        //                log.Debug("SqlException Number " + sqlEx.Number + " class " + sqlEx.Class, sqlEx);

        //            if ((sqlEx.Number == 1205) && (sqlEx.Class == 13))
        //            {
        //                if (retryCnt < maxRetry)
        //                {
        //                    retryCnt++;
        //                    System.Threading.Thread.Sleep(sleep);
        //                }
        //                else
        //                    throw new Adoor.Object.Disconnected.TransactionRetryException(sqlEx.Message, sqlEx);
        //            }
        //            else
        //                throw;
        //        }
        //    } while (true);
        //}

        //public override void ExecuteTable(DataTable dt, string commandText, System.Collections.ICollection args)
        //{
        //    int retryCnt = 0;
        //    do
        //    {
        //        try
        //        {
        //            base.ExecuteTable(dt, commandText, args);
        //            return;
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            if (log.IsDebugEnabled)
        //                log.Debug("SqlException Number " + sqlEx.Number + " class " + sqlEx.Class, sqlEx);

        //            if ((sqlEx.Number == 1205) && (sqlEx.Class == 13))
        //            {
        //                if (retryCnt < maxRetry)
        //                {
        //                    retryCnt++;
        //                    System.Threading.Thread.Sleep(sleep);
        //                }
        //                else
        //                    throw new Adoor.Object.Disconnected.TransactionRetryException(sqlEx.Message
        //                        + "\n"
        //                        + commandText
        //                        , sqlEx);
        //            }
        //            else
        //                throw;
        //        }
        //    } while (true);
        //}

        //public override void ExecuteTable(DataTable dt, string commandText, IDictionary<string, object> args)
        //{
        //    int retryCnt = 0;
        //    do
        //    {
        //        try
        //        {
        //            base.ExecuteTable(dt, commandText, args);
        //            return;
        //        }
        //        catch (SqlException sqlEx)
        //        {
        //            if (log.IsDebugEnabled)
        //                log.Debug("SqlException Number " + sqlEx.Number + " class " + sqlEx.Class, sqlEx);

        //            if ((sqlEx.Number == 1205) && (sqlEx.Class == 13))
        //            {
        //                if (retryCnt < maxRetry)
        //                {
        //                    retryCnt++;
        //                    System.Threading.Thread.Sleep(sleep);
        //                }
        //                else
        //                    throw new Adoor.Object.Disconnected.TransactionRetryException(sqlEx.Message
        //                        + "\n"
        //                        + commandText
        //                        , sqlEx);
        //            }
        //            else
        //                throw;
        //        }
        //    } while (true);
        //}

    }
}
